﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// 
    /// </summary>
    public partial class OfficeItemDeptCount
    {

        public DataSet GetAllList1(string deptNo, string startDate, string endDate) 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT g.FItemRequisitionApplyID, g.FItemNo,g.FApplyDeptNo,g.FDeptName,g.FApplyEmplNo,FEmpName,g.FItemName, g.FItemSpec,g.FQty, g.FAmount, g.FPrice,g.FAuditDate ");
            strSql.Append(" FROM ");
            strSql.Append(" (SELECT e.FItemRequisitionApplyID, e.FItemNo, e.FApplyDeptNo,f.FDeptName,e.FApplyEmplNo,e.FItemName, e.FItemSpec,e.FQty, e.FAmount, e.FPrice,e.FAuditDate ");
            strSql.Append(" FROM  ");
            strSql.Append(" (SELECT c.FItemNo, c.FQty, c.FAmount,c.FApplyDeptNo,c.FAuditDate,c.FItemRequisitionApplyID,c.FPrice,c.FApplyEmplNo,d.FItemName, d.FItemSpec ");
            strSql.Append(" FROM  ");
            strSql.Append(" (SELECT b.FItemNo, b.FQty, b.FAmount, a.FApplyDeptNo,a.FAuditDate,b.FItemRequisitionApplyID,b.FPrice,a.FApplyEmplNo ");
            strSql.Append(" FROM  dbo.t_ItemRequisitionApply AS a INNER JOIN ");
            strSql.Append(" dbo.t_ItemRequisitionApplyEntry AS b  ");
            strSql.Append(" ON a.FItemRequisitionApplyID = b.FItemRequisitionApplyID ");
            strSql.Append(" WHERE a.FStatus = 'A' ");
            if (deptNo != "")
            {
                strSql.Append(" and a.FApplyDeptNo = '" + deptNo + "'");
            }
            if (startDate != "")
            {
                strSql.Append(" and DATEDIFF(DAY,a.FAuditDate," + startDate + ")<=0 ");
            }
            if (endDate != "")
            {
                strSql.Append(" and DATEDIFF(DAY,a.FAuditDate," + endDate + ")>=0 ");
            }
            strSql.Append(" )AS c INNER JOIN ");
            strSql.Append(" dbo.t_Item AS d ON c.FItemNo = d.FItemNo) AS e INNER JOIN ");
            strSql.Append(" dbo.t_HRDept AS f ON e.FApplyDeptNo = f.FDeptNo )");
            strSql.Append(" AS g inner join t_HREmpl ON g.FApplyEmplNo = t_HREmpl.FEmplNo");
            strSql.Append(" order by FApplyDeptNo");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="deptNo"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataSet GetAllList(string deptNo, string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT e.FItemNo, e.FApplyDeptNo, f.FDeptName, e.FItemName, e.FItemSpec, e.fqy, e.amount");
            strSql.Append(" FROM (SELECT c.FItemNo, c.FApplyDeptNo, d.FItemName, c.fqy, c.amount, d.FItemSpec");
            strSql.Append(" FROM (SELECT b.FItemNo, a.FApplyDeptNo, SUM(b.FQty) AS fqy, SUM(b.FAmount) AS amount ");
            strSql.Append(" FROM dbo.t_ItemRequisitionApply AS a INNER JOIN dbo.t_ItemRequisitionApplyEntry AS b  ");
            strSql.Append(" ON a.FItemRequisitionApplyID = b.FItemRequisitionApplyID  ");
            strSql.Append(" WHERE a.FStatus = 'A' ");
            if (deptNo != "")
            {
                strSql.Append(" and a.FApplyDeptNo = '" + deptNo + "'");
            }
            if (startDate != "")
            {
                strSql.Append(" and DATEDIFF(DAY,a.FAuditDate," + startDate + ")<=0 ");
            }
            if (endDate != "")
            {
                strSql.Append(" and DATEDIFF(DAY,a.FAuditDate," + endDate + ")>=0 ");
            }
            strSql.Append(" GROUP BY b.FItemNo, a.FApplyDeptNo) AS c INNER JOIN");
            strSql.Append(" dbo.t_Item AS d ON c.FItemNo = d.FItemNo)");
            strSql.Append(" AS e INNER JOIN dbo.t_HRDept AS f ON e.FApplyDeptNo = f.FDeptNo ");
            strSql.Append(" ORDER BY e.FApplyDeptNo, e.FItemNo");

            return DbHelperSQL.Query(strSql.ToString());
        
        }


    }
}
